import unittest
import pandas as pd
import os
from io import BytesIO
from datetime import datetime
from app import app, db, DetectionResult


class TestExportRecordsRealDB(unittest.TestCase):
    def setUp(self):
        app.config['TESTING'] = True
        app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@localhost/plate_detection'
        self.client = app.test_client()

        # 创建保存目录
        self.export_dir = os.path.join(os.path.dirname(__file__), 'export_reports')
        os.makedirs(self.export_dir, exist_ok=True)

    def _get_expected_count(self):
        """获取数据库中的总记录数"""
        with app.app_context():
            return DetectionResult.query.count()

    def _save_excel_file(self, data):
        """保存Excel文件到项目目录"""
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        filename = f"export_{timestamp}.xlsx"
        filepath = os.path.join(self.export_dir, filename)

        with open(filepath, 'wb') as f:
            f.write(data)

        print(f"\n导出文件已保存至：{filepath}")

    def test_basic_export(self):
        """测试基本导出功能"""
        response = self.client.get('/records/export')
        self.assertEqual(response.status_code, 200)

        # 保存文件
        self._save_excel_file(response.data)

        # 验证数据
        df = pd.read_excel(BytesIO(response.data))
        self.assertEqual(len(df), self._get_expected_count())
        self.assertListEqual(
            list(df.columns),
            ['id', '车牌号', '入库时间', '出库时间', '置信度', '应付金额']
        )

    def test_data_integrity(self):
        """验证数据完整性"""
        response = self.client.get('/records/export')
        df = pd.read_excel(BytesIO(response.data))

        # 保存文件
        self._save_excel_file(response.data)

        # 验证格式
        self.assertIn('元', df['应付金额'].iloc[0])
        self.assertIn('%', df['置信度'].iloc[0])
        self.assertRegex(df['入库时间'].iloc[0], r'\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}')


if __name__ == '__main__':
    unittest.main(verbosity=2)
